<?php
namespace Admin\Controller;
use Think\Controller;
class BaksqlController extends BaseController
{
    private $ds = "\n\r\n\r";  // 每条sql语句的结尾符
    public $sqlEnd = ";";
    public function index()
    {   
        $M = M();
        $tabs = $M->query('SHOW TABLE STATUS');
        $total = 0;
        foreach ($tabs as $k => $v) 
        {
            $tabs[$k]['size'] = byteFormat($v['Data_length'] + $v['Index_length']);
            $total+=$v['data_length'] + $v['index_length']; 
        }
        $this->assign("list", $tabs);
        $this->assign("total", byteFormat($total));
        $this->assign("tables", count($tabs));
        unset($tabs,$total);
        $this->display();
    }
    public function import()
    {
        $total = 0;
        foreach (glob(C("DB_BACKUP").'*'.C('FILE_EXTENDS')) as $filename)
         {
           $total += filesize($filename);
           $arr[]=array(
            "filename"=>basename($filename,C("FILE_EXTENDS")),
            "size"=>filesize($filename),
            "time" => filectime($filename),
            'part' => 1,
            'compress' => 'GZ',
            'fname'=>substr(basename($filename,C("FILE_EXTENDS")), 15)
            );
         }

        if(!empty($_GET["file"]))
        {
            $this->restore(I("file"));
        }
        
        $this->assign("list", $arr);
        $this->assign("total", byteFormat($total));
        $this->assign("tables", count($arr));
        unset($arr,$total);
        $this->display();    
    }
    public function truncate()
    {
        $table=I("table");
        if(!empty($table))
        {
           $M = M();
           $result = $M->execute('TRUNCATE TABLE '.$table);
           if(empty($result))
           {
               echo "清空".$table."表数据成功！";
           }
           else
           {
              echo "清空".$table."表表数据失败！";
           }
        }
        unset($table,$M,$result);
    }
    /** 删除备份文件 */
    public function delfile($time = 0)
    {    
        if($time)
        {
            $name  = $time.C("FILE_EXTENDS");
            $path  = realpath(C('DB_BACKUP')) . DIRECTORY_SEPARATOR . $name;  
            array_map("unlink", glob($path));
            if(count(glob($path)))
            {
                $this->error('备份文件删除失败，请检查权限！');
            } 
            else 
            {
                $this->success('文件删除成功！','',3);
            }
        } 
        else 
        {
            $this->error('参数错误！','',3);
        }
         unset($time,$name,$path);
    }
    /**删除数据表* */
    public function delete()
    {
        $table=I("table");
        if(!empty($table))
        {
            $M =  M();
            $result = $M -> execute("DROP TABLE   ".$table);
            if(empty($result))
            {
              echo "删除表成功！";
            }
            else
            {
              echo "删除表失败！";
            }
        }
         unset($table,$M,$result);
    }
    /** 优化表*/
     public function optimize()
     {
            $table=I("table");
            if(!empty($table))
            {
                $M =  M();
                $sql="OPTIMIZE TABLE  `".$table.'`';
                $result = $M -> query($sql);
                if($result)
                {
                  echo "优化完成！";
                }
                else
                {
                  echo "优化出错！"; 
                }
            }
            unset($table,$M,$result);
     }
    
    /**修复表*/
    public function repair()
    {
        if(IS_POST)
        {
            $table=I("table"); 
            if(!empty($table))
            {
                $M =  M();
                $sql="REPAIR TABLE  `".$table.'`';
                $result = $M -> query($sql);
                if($result)
                {
                  echo "修复完成！";
                }
                else
                {
                  echo "修复出错！";
                }
            }
        }
        else
        {
            $M = M();
            $tabs = $M->query('SHOW TABLE STATUS');
            $total = 0;
            foreach ($tabs as $k => $v) 
            {
                $tabs[$k]['size'] = byteFormat($v['Data_length'] + $v['Index_length']);
                $total+=$v['data_length'] + $v['index_length']; 
            }

            $this->assign("list", $tabs);
            $this->assign("total", byteFormat($total));
            $this->assign("tables", count($tabs));
            $this->display();
       }
        unset($table,$M,$result,$sql,$tabs,$total,$k);
    }
    
    /** getTables 获取数据库表列表*/
    public function getTables() 
    {
        $M = M();
        $res = $M->query ( "SHOW TABLES;" );
        $tables = array ();
        foreach ( $res as $row ) 
        {
            foreach ($row as $v)
            {
                $tables[]=$v;
            }
        }
        return $tables;
        unset($M, $res,$tables,$res,$row,$v);
    }
   
    /**插入数据库备份基础信息 */
    private function _base() 
    {
        $value = '';
        $value .= '-- MySQL database dump' . $this->ds;
        $value .= '-- Created by DBAction class, Power By TaoTao. ' . $this->ds;
        $value .= '-- http://www.huichuang.tv' . $this->ds;
        $value .= '--' . $this->ds;
        $value .= '-- 主机: ' . $this->host . $this->ds;
        $value .= '-- 生成日期: ' . date ( 'Y' ) . ' 年  ' . date ( 'm' ) . ' 月 ' . date ( 'd' ) . ' 日 ' . date ( 'H:i' ) . $this->ds;
        $value .= '-- MySQL版本: ' . mysql_get_server_info () . $this->ds;
        $value .= '-- PHP 版本: ' . phpversion () . $this->ds;
        $value .= $this->ds;
        $value .= '--' . $this->ds;
        $value .= '-- 数据库: `' . C("DB_NAME") . '`'. $this->ds;
        $value .= '--' . $this->ds ;
        $value .= '-- -------------------------------------------------------';
        $value .= $this->ds . $this->ds;
        return $value;
        unset($value);
    }
   
    /** 插入表结构 */
    private function _insert_table_structure($table) 
    {
        $sql = '';
        $M = M();
        $sql .= "DROP TABLE IF EXISTS `" . $table . '`' . $this->sqlEnd . $this->ds;  // 如果存在则删除表
        $res = $M->query ('SHOW CREATE TABLE ' . $table);  // 获取详细表信息 
        $sql .= $res [0]["create table"];
        $sql .= $this->sqlEnd . $this->ds; 

        return $sql;
        unset($sql,$M,$res,$table);
    }
    /**插入语句构造*/
    private function _insert_records($table) 
    {
        $M = M();//sql字段逗号分割
        $res = $M->query ( 'select * FROM `' . $table . '`' );    
        foreach ($res as $val)// 循环每个子段下面的内容
        {
            $comma = 0;
            $insert .= "INSERT INTO `" . $table . "` VALUES(";
            foreach ($val as $v)
            {
                $insert.=$comma == 0 ? "" : ",";
                $insert.= ( "'" . mysql_escape_string ( $v ) . "'");
                $comma++; 
            }
            $insert .= ");" . $this->ds;
            
        }
        return $insert;
        unset($insert,$M,$res,$table,$comma,$v);
    }

     private function _insert_record($table, $num_fields, $record) { 
        // sql字段逗号分割 
        $insert = ''; 
        $comma = ""; 
        $insert .= "INSERT INTO `" . $table . "` VALUES("; 
        // 循环每个子段下面的内容 
        for($i = 0; $i < $num_fields; $i ++) { 
            $insert .= ($comma . "'" . mysql_escape_string ( $record [$i] ) . "'"); 
            $comma = ","; 
        } 
        $insert .= ");" . $this->ds; 
        return $insert; 
    } 
    /**写入文件 */
    private function _write_file($sql, $filename, $dir)
    {
        $dir = C("DB_BACKUP");  // 创建目录
        if (! is_dir ( $dir )) 
        {
            mkdir ( $dir, 0777, true );
        }
        $re = true;
        if (! @$fp = fopen ( $dir . $filename, "w+" )) 
        {
            $re = false;
            $msg .= "打开文件失败！";
        }
        if (! @fwrite ( $fp, $sql )) 
        {
            $re = false;
            $msg .= "写入文件失败，请文件是否可写";
        }
        if (! @fclose ( $fp ))
        {
            $re = false;
            $msg .= "关闭文件失败！";
        }
        return $re;
        unset($dir,$re,$filename,$sql,$msg,$fp);
    }
    function sql_dumptable($table, $startfrom = 0, $currsize = 0,$sizelimit)
    {
        session($startrow, $value='');
        $M = M();
        if(!isset($tabledump)) $tabledump = '';
        $offset = 100;
        if(!$startfrom)
        {
            $createtable = $M->query("SHOW CREATE TABLE $table");
            $create =$createtable [0]["create table"];      
            $tabledump = "DROP TABLE IF EXISTS $table;\n";
            $tabledump .= $create[1].";\n\n";
        }

        $tabledumped = 0;
        $numrows = $offset;

        while($currsize + strlen($tabledump) < $sizelimit * 1000 && $numrows == $offset)
        {
            $tabledumped = 1;
            
            $tabledump=$this->_insert_recordssss($table,$offset);
           
            $startfrom += $offset;

        }
        //$startrow = $startfrom;
        session($startrow, $value=$startfrom);
        $tabledump .= "\n";
        return $tabledump;
    }
   
    /***数据库备份 参数：备份哪个表(可选),备份目录(可选，默认为backup),分卷大小(可选,默认2048，即2M)*/
    public function export() 
    {
        $tablename = I("table");
        $dir = C("DB_BACKUP");
        if (! is_dir($dir)) // 创建目录
        {   
            mkdir ( $dir, 0777, true ) or die ( '创建文件夹失败' );
        }
        $sql = '';
        $size = C("SIZE_BACKUP") * 1024 * 1024;
       
        // 只备份某个表 
        if(!empty($tablename))
        {   
                $sql .= $this->_base();
                $sql .= $this->_insert_table_structure($tablename);
                $sql .= $this->_insert_records($tablename);
                $filename = date ( 'YmdHis' ). "_" . $tablename . C("FILE_EXTENDS"); 
                if ($this->_write_file ( $sql, $filename, $dir )) 
                { 
                    $this->success("数据备份完成！",'./index',3);
                } 
                else
                { 
                   $this->success("数据备份失败！",'./index',3);
                   return false; 
                } 
        }
           
           
        
        else //备份全部表 
        {  
            if ($tables = $M->query( "show table status from " . C("DB_NAME") ))
            {
                $msg .= "读取数据库结构成功！<br />";
            } 
            else 
            {
                exit ( "读取数据库结构失败！<br />" );$this->error("读取数据库结构失败！<br />");
            }
            $sql .= $this->_base (); // 插入dump信息
            $filename = date ( 'YmdHis' ) . "_all"; // 文件名前面部分
            $tables = $M->query ( 'SHOW TABLES' );  // 查出所有表
            $p = 1; // 第几分卷
            foreach  ( $tables as $value)// 循环所有表
             {  
                foreach ($value as $v)
                 {
                    $sql .= $this->_insert_table_structure ( $v );// 获取表结构
                    $sql .= $this->_insert_record ( $v );       // 单条记录
                  }
             }
                    if (strlen ( $sql ) >= $size * 1024)  // 如果大于分卷大小，则写入文件
                     {
                        $file = $filename . "_v" . $p . ".sql";  // 写入文件
                        if ($this->_write_file ( $sql, $file, $dir )) 
                        {
                            $this->msg .= "-卷-" . $p . "-数据备份完成,生成备份文件<span style='color:#f00;'>$dir$file</span><br />";
                        } 
                        else
                        {
                            $this->msg .= "备份卷-" . $p . "-失败<br />";
                            return false;
                        }
                        $p ++;// 下一个分卷
                        $sql = "";   // 重置$sql变量为空，重新计算该变量大小
                    }
                    if ($sql != "") // sql大小不够分卷大小
                     {
                        $filename .=  ".sql";
                        if ($this->_write_file ( $sql, $filename, $dir )) 
                        {
                            $msg .= "数据库-" . C("DB_NAME") . "-卷-" . $p. '<br />'. "-数据备份完成,生成备份文件 <span style='color:#f00;'>$dir$filename</span><br />";
                        } 
                        else
                        {
                            $msg .= "备份卷-" . $p . "-失败<br />";
                            return false;
                        }
                    }
        }
        echo $msg; 
        unset($tablename,$dir,$sql,$tables,$value,$filename,$p,$M,$msg);
    }
    /**导入备份数据 说明：分卷文件格式20120516211738_all_v1.sql 参数：文件路径(必填)*/
    function restore()
    {
        $file=I('file').C("FILE_EXTENDS");
        $sqlfile=C("DB_BACKUP").$file;
        if (! file_exists ( $sqlfile ))
        {
            exit ( "文件不存在！请检查" );
        }
        $sqlpath = pathinfo ( $sqlfile );
        $volume = explode ( "_v", $sqlfile );
        $volume_path = $volume [0];
        if (empty ( $volume [1] )) 
        {
           
            if ($this->_import ( $sqlfile )) 
            {
              $this->success("导入成功！");
            }
           else
            {
                $this->error("导入失败！");
            }
        } 
        else 
        {
            $volume_id = explode ( ".sq", $volume [1] );
            $volume_id = intval ( $volume_id [0] );
            while ( $volume_id ) {
                $tmpfile = $volume_path . "_v" . $volume_id . C("FILE_EXTENDS");
                if (file_exists ( $tmpfile )) 
                {
                    if ($this->_import ( $tmpfile )) 
                    {
                      
                    } 
                    else 
                    {
                        $volume_id = $volume_id ? $volume_id :1;
                        exit ( "导入分卷：<span style='color:#f00;'>" . $tmpfile . '</span>失败！可能是数据库结构已损坏！请尝试从分卷1开始导入' );
                    }
                } else {
                    echo $msg;
                }
                $volume_id ++;
            }
        }
        unset($file,$sqlfile,$sqlpath,$volume,$volume_path,$volume_id,$tmpfile,$volume_path,$msg);
    }
    /**将sql导入到数据库（普通导入） */
    private function _import($sqlfile)
     {
        $content = file_get_contents($sqlfile);
        $array = explode(";".$this->ds, $content);
        for($i=0; $i<count($array)-1; $i++)
        {
            $this->_insert_into($array[$i].";");
        }
        return true;
        unset($content,$array,$sqlfile);
    }
    //插入单条sql语句
    private function _insert_into($sql)
    {
        $M=M();
        if (!$M->execute($sql))
        {
           $msg = mysql_error ();
           return false;
        }
        unset($M,$msg,$sql);
    }
    // 锁定数据库，以免备份或导入时出错
    private function lock($tablename, $op = "WRITE")
     {
        $M=M();
        if ($M->query( "lock tables " . $tablename . " " . $op ))
            return true;
        else
            return false;
        unset($M,$tablename,$op);
    }
    // 解锁
    private function unlock()
    {
        $M=M();
        if ($M->query ( "unlock tables" ))
            return true;
        else
            return false;
      unset($M);
    }
    
}
   
